package com.groupThree.dao.imp;
import com.groupThree.bean.User;
import com.groupThree.dao.BaseUserDao;
import com.groupThree.util.DruidUtil;


import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class UserDaoMySql implements BaseUserDao {
    public static final String SQL_CONSOLE = "SELECT COUNT(U_ID) data_size,COUNT(TO_DAYS(U_REGISTERTIME)=TO_DAYS(NOW()) OR NULL) data_day FROM EUSER";
    public static final String SQL_FIND_ALL ="SELECT * FROM EUSER";
    //用于分页查询数据库中的用户信息
    public static final String SQL_FIND_LIMIT = "SELECT * FROM EUSER LIMIT ?,?";
    //根据手机号查询
    public static final String SQL_FIND_BY_U_PHONE ="SELECT * FROM EUSER WHERE U_PHONE =?";
    //录入用户
    public static final String SQL_INSERT = "INSERT INTO EUSER(U_name, U_phone,U_IDCARD ,U_password, u_registertime ) VALUES (?,?,?,?,NOW())";
    public static final String SQL_UPDATE = "UPDATE EUSER SET U_name =?, U_phone =?, U_idcard =?, U_password =? WHERE U_ID = ?";
    public static final String SQL_DELETE = "DELETE FROM EUSER WHERE U_PHONE=?";
    private static final String SQL_FIND_SAME_U_PHONE = "SELECT * FROM  EUSER WHERE U_PHONE = ?";
    private static final String SQL_FIND_SAME_U_IDCARD = "SELECT * FROM  EUSER WHERE U_IDCARD = ?";
    private static final String SQL_DELETE_U_ID = "DELETE FROM EUSER WHERE U_ID=?";
    private static final String SQL_UPDATE_LOGIN_TIME = "UPDATE EUSER SET u_logintime = NOW() WHERE U_PHONE = ?";
    private static final String SQL_LOGIN_INSERT ="INSERT INTO EUSER(U_name, U_phone, u_registertime ) VALUES (?,?,NOW())" ;

    @Override
    public List<Map<String, Integer>> console() {
        ArrayList<Map<String,Integer>> data = new ArrayList<>();
        //获取连接
        Connection conn = DruidUtil.getConnection();

        PreparedStatement state = null;
        ResultSet result = null ;

        try {
            //初始化sql
            state = conn.prepareStatement(SQL_CONSOLE);
            //执行sql
            result = state.executeQuery();
            if (result.next()){
                int data_size = result.getInt("data_size");
                int data_day = result.getInt("data_day");
                Map data1 = new HashMap();
                data1.put("data_size",data_size);
                data1.put("data_day",data_day);
                data.add(data1);
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DruidUtil.close(conn,state,result);
        }
        return data;
    }

    @Override
    public List<User> findAll(boolean limit, int offset, int pageNumber) {
        ArrayList<User> data = new ArrayList<>();
        //获取连接
        Connection conn = DruidUtil.getConnection();

        PreparedStatement state = null;
        ResultSet result = null ;

        try {
            if (limit){
                state = conn.prepareStatement(SQL_FIND_LIMIT);
                state.setInt(1,offset);
                state.setInt(2,pageNumber);
            }else {
                //初始化sql
                state = conn.prepareStatement(SQL_FIND_ALL);
            }

            //执行sql
            result = state.executeQuery();
            //获取查询结果
            while (result.next()){
                int u_id = result.getInt("U_ID");
                String u_name = result.getString("U_NAME");
                String u_phone = result.getString("U_PHONE");
                String u_password = result.getString("U_PASSWORD");
                Timestamp u_registertime = result.getTimestamp("U_REGISTERTIME");
                Timestamp u_logintime = result.getTimestamp("U_LOGINTIME");
                User u = new User();
                u.setU_id(u_id);
                u.setU_name(u_name);
                u.setU_phone(u_phone);
                u.setU_password(u_password);
                u.setU_registertime(u_registertime);
                u.setU_logintime(u_logintime);

                data.add(u);

            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DruidUtil.close(conn,state,result);
        }
        return data;
    }

    @Override
    public User findByU_Phone(String u_phone) {
        //获取连接
        Connection conn = DruidUtil.getConnection();

        PreparedStatement state = null;
        ResultSet result = null ;

        try {
            //初始化sql
            state = conn.prepareStatement(SQL_FIND_BY_U_PHONE);
            //填充参数
            state.setString(1,u_phone);
            //执行sql
            result = state.executeQuery();
            if (result.next()){
                int u_id = result.getInt("u_id");
                String u_name = result.getString("u_name");
                String u_idcard = result.getString("u_idcard");
                String u_password = result.getString("u_password");
                Timestamp u_registertime = result.getTimestamp("u_registertime");
                Timestamp u_logintime = result.getTimestamp("u_logintime");
                User user = new User(u_id,u_name,u_phone,u_idcard,u_password,u_registertime,u_logintime);
                return user;
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DruidUtil.close(conn,state,result);
        }
        return null;
    }

    @Override
    public boolean insert(User user) {
        //获取连接
        Connection conn = DruidUtil.getConnection();

        PreparedStatement state = null;
        ResultSet result = null ;

        try {
            //初始化sql
            state = conn.prepareStatement(SQL_INSERT);
            state.setString(1,user.getU_name());
            state.setString(2,user.getU_phone());
            state.setString(3,user.getU_idcard());
            state.setString(4,user.getU_password());
            //执行sql
             return state.executeUpdate()>0?true:false;

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DruidUtil.close(conn,state,null);
        }
        return false;
    }

    @Override
    public boolean loginInsert(User user) {
        //获取连接
        Connection conn = DruidUtil.getConnection();

        PreparedStatement state = null;
        ResultSet result = null ;

        try {
            //初始化sql
            state = conn.prepareStatement(SQL_LOGIN_INSERT);
            state.setString(1,user.getU_name());
            state.setString(2,user.getU_phone());
            //执行sql
            return state.executeUpdate()>0?true:false;

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DruidUtil.close(conn,state,null);
        }
        return false;
    }

    @Override
    public boolean update(int u_id, User newUser) {
        //获取连接
        Connection conn = DruidUtil.getConnection();

        PreparedStatement state = null;
        ResultSet result = null ;

        try {
            //初始化sql
            state = conn.prepareStatement(SQL_UPDATE);
            state.setString(1,newUser.getU_name());
            state.setString(2,newUser.getU_phone());
            state.setString(3,newUser.getU_idcard());
            state.setString(4,newUser.getU_password());
            state.setInt(5,newUser.getU_id());
            //执行sql
            return state.executeUpdate()>0?true:false;

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DruidUtil.close(conn,state,null);
        }
        return false;
    }

    @Override
    public boolean delete(int u_id) {
        //获取连接
        Connection conn = DruidUtil.getConnection();

        PreparedStatement state = null;
        ResultSet result = null ;
        //String id = String.valueOf(u_id);

        try {
            //初始化sql
            state = conn.prepareStatement(SQL_DELETE_U_ID);
            state.setInt(1,u_id);
            //执行sql
            return state.executeUpdate()>0?true:false;

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DruidUtil.close(conn,state,null);
        }
        return false;
    }

    @Override
    public boolean delete(String u_phone) {
        //获取连接
        Connection conn = DruidUtil.getConnection();

        PreparedStatement state = null;
        ResultSet result = null ;

        try {
            //初始化sql
            state = conn.prepareStatement(SQL_DELETE);
            state.setString(1,u_phone);
            //执行sql
            return state.executeUpdate()>0?true:false;

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DruidUtil.close(conn,state,null);
        }
        return false;
    }

    @Override
    public boolean findSameu_Phone(String u_phone) {
        //获取连接
        Connection conn = DruidUtil.getConnection();

        PreparedStatement state = null;
        ResultSet result = null ;

        try {
            //初始化sql
            state = conn.prepareStatement(SQL_FIND_BY_U_PHONE);
            state.setString(1,u_phone);
            //执行sql
            result = state.executeQuery();

            if (result.next()){
                return true;
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DruidUtil.close(conn,state,result);
        }
        return false;
    }

    @Override
    public boolean findSameU_Idcard(String u_idcard) {
        //获取连接
        Connection conn = DruidUtil.getConnection();

        PreparedStatement state = null;
        ResultSet result = null ;

        try {
            //初始化sql
            state = conn.prepareStatement(SQL_FIND_SAME_U_IDCARD);
            state.setString(1,u_idcard);
            //执行sql
            result = state.executeQuery();
            if (result.next()){
                if (result.getString(u_idcard) == null ){
                    //身份证号为空
                    return false;
                }else {
                    return true;
                }

            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DruidUtil.close(conn,state,result);
        }
        return false;
    }

    @Override
    public boolean updateLoginTime(String u_phone) {
        //获取连接
        Connection conn = DruidUtil.getConnection();

        PreparedStatement state = null;
        ResultSet result = null ;

        try {
            //初始化sql
            state = conn.prepareStatement(SQL_UPDATE_LOGIN_TIME);
           state.setString(1,u_phone);
            //执行sql
            return state.executeUpdate()>0?true:false;

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DruidUtil.close(conn,state,null);
        }


        return false;
    }
}
